
#Bibliotheken laden
import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import plotly_express as px


df1 = pd.read_csv('part1.csv',
                  sep='\n',
                  nrows=2
                 )
df1


df2 = df1 = pd.read_csv('part2.csv',
                  sep='\n',
                  nrows=2
                 )
df2


df1 = pd.read_csv('part1.csv',
                  sep=',',
                  index_col=0
                 )

df1.head(5)


df1.tail(5)


df2 = pd.read_csv('part2.csv',
                  sep='#',
                  index_col=0
                 )

df2.head(5)


df2.tail(5)


df1.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


df2.dtypes

funded_amount         float64
loan_amount           float64
activity               object
sector                 object
use                    object
country_code           object
country                object
region                 object
currency               object
term_in_months        float64
lender_count            int64
borrower_genders       object
repayment_interval     object
dtype: object


df1.columns

Index(['funded_amount', 'loan_amount', 'activity', 'sector', 'use',
       'country_code', 'country', 'region', 'currency', 'term_in_months',
       'lender_count', 'borrower_genders', 'repayment_interval'],
      dtype='object')


df2.columns

Index(['funded_amount', 'loan_amount', 'activity', 'sector', 'use',
       'country_code', 'country', 'region', 'currency', 'term_in_months',
       'lender_count', 'borrower_genders', 'repayment_interval'],
      dtype='object')


df1.shape, df2.shape

((335000, 13), (336205, 13))


# Kontrolle welche Größe neuer Dataframe durch Joinen haben müsste
len(df1)+len(df2)

671205


# beide Dataframes können zeilenweise gemerged werden, 
# da sie die gleiche Struktur haben, die selben Spaltennamen, die selben Datentypen
df_funds = pd.concat([df1, df2])
df_funds.shape

(671205, 13)


# selbes Ergebnis wie bei df1.head()
df_funds.head()


# selbes Ergebnis wie bei df2.tail()
df_funds.tail()


# Resetten des Index, da Index von df2 nur angehängt wurde
df_funds.reset_index(inplace=True, drop=True)
df_funds.tail()


df_funds.info() # welche Datentypen und ob NaN Werte vorliegen

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 671205 entries, 0 to 671204
Data columns (total 13 columns):
 #   Column              Non-Null Count   Dtype  
---  ------              --------------   -----  
 0   funded_amount       671205 non-null  float64
 1   loan_amount         671205 non-null  float64
 2   activity            671205 non-null  object 
 3   sector              671205 non-null  object 
 4   use                 666973 non-null  object 
 5   country_code        671197 non-null  object 
 6   country             671205 non-null  object 
 7   region              614405 non-null  object 
 8   currency            671205 non-null  object 
 9   term_in_months      671205 non-null  float64
 10  lender_count        671205 non-null  int64  
 11  borrower_genders    666984 non-null  object 
 12  repayment_interval  671205 non-null  object 
dtypes: float64(3), int64(1), object(9)
memory usage: 66.6+ MB


df_funds.isnull().sum() # Anzahl systemseitig erkannter NaN Werte

funded_amount             0
loan_amount               0
activity                  0
sector                    0
use                    4232
country_code              8
country                   0
region                56800
currency                  0
term_in_months            0
lender_count              0
borrower_genders       4221
repayment_interval        0
dtype: int64


df_funds.describe() # kleine Statis. Übersicht
# hier müssten alle numerischen Spalten aufgelistet sein


# # Pairplot
# my_pairplot = sns.pairplot(data=df_funds,
#                            corner=True,
#                            hue='sector'
#                           )


fig = px.scatter_matrix(df_funds,
                        color="sector",
                        dimensions=['funded_amount', 'loan_amount', 'term_in_months', 'lender_count']
                       )
fig.show()


df_funds_corr = df_funds.corr(method='pearson') # Defaulteinstellung ist Pearson Correlation als method
#pearson : standard correlation coefficient
df_funds_corr


sns.heatmap(df_funds_corr, vmin=-1, vmax=1,center=0,
            linewidths=.5, 
            cmap='coolwarm',
            annot=True).set_title('Korrelationsmatrix')

Text(0.5, 1.0, 'Korrelationsmatrix')


df_funds.info() # welche Datentypen und ob NaN Werte vorliegen

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 671205 entries, 0 to 671204
Data columns (total 13 columns):
 #   Column              Non-Null Count   Dtype  
---  ------              --------------   -----  
 0   funded_amount       671205 non-null  float64
 1   loan_amount         671205 non-null  float64
 2   activity            671205 non-null  object 
 3   sector              671205 non-null  object 
 4   use                 666973 non-null  object 
 5   country_code        671197 non-null  object 
 6   country             671205 non-null  object 
 7   region              614405 non-null  object 
 8   currency            671205 non-null  object 
 9   term_in_months      671205 non-null  float64
 10  lender_count        671205 non-null  int64  
 11  borrower_genders    666984 non-null  object 
 12  repayment_interval  671205 non-null  object 
dtypes: float64(3), int64(1), object(9)
memory usage: 66.6+ MB


df_funds.isnull().sum()

funded_amount             0
loan_amount               0
activity                  0
sector                    0
use                    4232
country_code              8
country                   0
region                56800
currency                  0
term_in_months            0
lender_count              0
borrower_genders       4221
repayment_interval        0
dtype: int64


# im Voraus keine NaN gefunden
df_funds['activity'].unique(), df_funds['activity'].nunique()
# keine Synonyme gefunden

(array(['Fruits & Vegetables', 'Rickshaw', 'Transportation', 'Embroidery',
        'Milk Sales', 'Services', 'Dairy', 'Beauty Salon', 'Manufacturing',
        'Food Production/Sales', 'Wholesale', 'General Store',
        'Clothing Sales', 'Poultry', 'Tailoring', 'Sewing', 'Bakery',
        'Restaurant', 'Food Stall', 'Farming', 'Construction Supplies',
        'Personal Products Sales', 'Home Products Sales',
        'Natural Medicines', 'Fish Selling', 'Education provider',
        'Shoe Sales', 'Machinery Rental', 'Butcher Shop', 'Pigs',
        'Personal Expenses', 'Food Market', 'Cosmetics Sales',
        'Personal Housing Expenses', 'Retail', 'Energy', 'Grocery Store',
        'Construction', 'Agriculture', 'Motorcycle Transport',
        'Charcoal Sales', 'Food', 'Pharmacy', 'Fishing', 'Timber Sales',
        'Cattle', 'Electronics Repair', 'Electronics Sales', 'Vehicle',
        'Cafe', 'Blacksmith', 'Higher education costs', 'Used Clothing',
        'Fuel/Firewood', 'Upholstery', 'Catering', 'Animal Sales',
        'Cereals', 'Vehicle Repairs', 'Arts',
        'Cloth & Dressmaking Supplies', 'Mobile Phones', 'Spare Parts',
        'Clothing', 'Metal Shop', 'Barber Shop', 'Furniture Making',
        'Crafts', 'Home Energy', 'Home Appliances', 'Wedding Expenses',
        'Taxi', 'Secretarial Services', 'Livestock', 'Property',
        'Recycling', 'Farm Supplies', 'Auto Repair', 'Beverages',
        'Plastics Sales', 'Electrical Goods', 'Carpentry', 'Photography',
        'Jewelry', 'Bricks', 'Pub', 'Phone Use Sales',
        'Water Distribution', 'Paper Sales', 'Computers',
        'Liquor Store / Off-License', 'Utilities', 'Knitting', 'Weaving',
        'Party Supplies', 'Medical Clinic', 'Internet Cafe',
        'Consumer Goods', 'Cement', 'Electrician',
        'Primary/secondary school costs', 'Veterinary Sales',
        'Land Rental', 'Laundry', 'Call Center', 'Perfumes', 'Hotel',
        'Motorcycle Repair', 'Movie Tapes & DVDs', 'Quarrying',
        'Personal Medical Expenses', 'Bookstore', 'Decorations Sales',
        'Recycled Materials', 'Office Supplies', 'Souvenir Sales',
        'Renewable Energy Products', 'Health', 'Printing', 'Phone Repair',
        'Traveling Sales', 'Flowers', 'Bicycle Repair', 'Entertainment',
        'Phone Accessories', 'Hardware', 'Used Shoes',
        'Music Discs & Tapes', 'Games', 'Balut-Making', 'Textiles',
        'Child Care', 'Goods Distribution', 'Florist', 'Cobbler', 'Dental',
        'Bookbinding', 'Cheese Making', 'Bicycle Sales', 'Well digging',
        'Technology', 'Musical Performance', 'Waste Management', 'Film',
        'Tourism', 'Musical Instruments', 'Religious Articles',
        'Machine Shop', 'Cleaning Services', 'Sporting Good Sales',
        'Patchwork', 'Funerals', 'Air Conditioning', 'Communications',
        'Adult Care', 'Landscaping / Gardening', 'Aquaculture',
        'Beekeeping', 'Event Planning', 'Celebrations', 'Computer',
        'Personal Care Products', 'Mobile Transactions'], dtype=object),
 163)


# im Voraus keine NaN gefunden
df_funds['sector'].unique(), df_funds['sector'].nunique()
# keine Synonyme gefunden

(array(['Food', 'Transportation', 'Arts', 'Services', 'Agriculture',
        'Manufacturing', 'Wholesale', 'Retail', 'Clothing', 'Construction',
        'Health', 'Education', 'Personal Use', 'Housing', 'Entertainment'],
       dtype=object),
 15)


# im Voraus 4232 NaN gefunden
df_funds['use'].unique(), df_funds['use'].nunique()
# zuviele Uniques, um Synonyme herauszufinden

(array(['To buy seasonal, fresh fruits to sell. ',
        'to repair and maintain the auto rickshaw used in their business.',
        'To repair their old cycle-van and buy another one to rent out as a source of income',
        ...,
        'Pretend the issue with loan got addressed by Kiva Coordinator.',
        'Kiva Coordinator replaced loan use. Should see this in viewdiff.',
        'Edited loan use in english.'], dtype=object),
 424912)


# Zeilen von use, die mit [ beginnen
s_use = df_funds['use'].str.contains('^\[', case=False, na=False)
s_use

0         False
1         False
2         False
3         False
4         False
          ...  
671200     True
671201     True
671202    False
671203     True
671204     True
Name: use, Length: 671205, dtype: bool


# Anzeige der Zeilen, in dem s_use True ist
df_funds.loc[s_use, 'use']

333863                     [to buy] watermelon and bananas.
355352         [to purchase] seeds, manure and fertilizers.
581965    [to purchase] an 80-watt solar system with six...
671160    [True, u'to start a turducken farm.'] - this l...
671163    [True, u'para compara: cemento, arenya y ladri...
671166    [True, u'to start a turducken farm.'] - this l...
671177    [True, u'to start a turducken farm.'] - this l...
671179    [True, u'para compara: cemento, arenya y ladri...
671186    [True, u'para compara: cemento, arenya y ladri...
671187    [True, u'to start a turducken farm.'] - this l...
671189    [True, u'to start a turducken farm.'] - this l...
671190    [True, u'to start a turducken farm.'] - this l...
671199    [True, u'para compara: cemento, arenya y ladri...
671200    [True, u'para compara: cemento, arenya y ladri...
671201    [True, u'to start a turducken farm.'] - this l...
671203    [True, u'to start a turducken farm.'] - this l...
671204    [True, u'to start a turducken farm.'] - this l...
Name: use, dtype: object


# im Voraus 8 NaN gefunden
df_funds['country_code'].unique(), df_funds['country_code'].nunique(dropna=False)
# keine Synonyme gefunden

(array(['PK', 'IN', 'KE', 'NI', 'SV', 'TZ', 'PH', 'PE', 'SN', 'KH', 'LR',
        'VN', 'IQ', 'HN', 'PS', 'MN', 'US', 'ML', 'CO', 'TJ', 'GT', 'EC',
        'BO', 'YE', 'GH', 'SL', 'HT', 'CL', 'JO', 'UG', 'BI', 'BF', 'TL',
        'ID', 'GE', 'UA', 'XK', 'AL', 'CD', 'CR', 'SO', 'ZW', 'CM', 'TR',
        'AZ', 'DO', 'BR', 'MX', 'KG', 'AM', 'PY', 'LB', 'WS', 'IL', 'RW',
        'ZM', 'NP', 'CG', 'MZ', 'ZA', 'TG', 'BJ', 'BZ', 'SR', 'TH', 'NG',
        'MR', 'VU', 'PA', 'VI', 'VC', 'LA', 'MW', 'MM', 'MD', 'SS', 'SB',
        'CN', 'EG', 'GU', 'AF', 'MG', nan, 'PR', 'LS', 'CI', 'BT'],
       dtype=object),
 87)


# im Voraus keine NaN gefunden
df_funds['country'].unique(), df_funds['country'].nunique()
# es gibt 87 Länder und 87 Ländercodes inklusive NaN. Passt

(array(['Pakistan', 'India', 'Kenya', 'Nicaragua', 'El Salvador',
        'Tanzania', 'Philippines', 'Peru', 'Senegal', 'Cambodia',
        'Liberia', 'Vietnam', 'Iraq', 'Honduras', 'Palestine', 'Mongolia',
        'United States', 'Mali', 'Colombia', 'Tajikistan', 'Guatemala',
        'Ecuador', 'Bolivia', 'Yemen', 'Ghana', 'Sierra Leone', 'Haiti',
        'Chile', 'Jordan', 'Uganda', 'Burundi', 'Burkina Faso',
        'Timor-Leste', 'Indonesia', 'Georgia', 'Ukraine', 'Kosovo',
        'Albania', 'The Democratic Republic of the Congo', 'Costa Rica',
        'Somalia', 'Zimbabwe', 'Cameroon', 'Turkey', 'Azerbaijan',
        'Dominican Republic', 'Brazil', 'Mexico', 'Kyrgyzstan', 'Armenia',
        'Paraguay', 'Lebanon', 'Samoa', 'Israel', 'Rwanda', 'Zambia',
        'Nepal', 'Congo', 'Mozambique', 'South Africa', 'Togo', 'Benin',
        'Belize', 'Suriname', 'Thailand', 'Nigeria', 'Mauritania',
        'Vanuatu', 'Panama', 'Virgin Islands',
        'Saint Vincent and the Grenadines',
        "Lao People's Democratic Republic", 'Malawi', 'Myanmar (Burma)',
        'Moldova', 'South Sudan', 'Solomon Islands', 'China', 'Egypt',
        'Guam', 'Afghanistan', 'Madagascar', 'Namibia', 'Puerto Rico',
        'Lesotho', "Cote D'Ivoire", 'Bhutan'], dtype=object),
 87)


# im Voraus 56800 NaN gefunden
df_funds['region'].unique(), df_funds['region'].nunique()
# zu viele unique Values, um Synonyme zu finden. 
# Regionen werden in der EDA nicht betrachtet, Länder dagegen schon.

(array(['Lahore', 'Maynaguri', 'Abdul Hakeem', ..., 'Gbenikoro Village',
        'Morimaraia', 'alejandria'], dtype=object),
 12695)


# im Voraus keine NaN gefunden
df_funds['currency'].unique(), df_funds['currency'].nunique()
# keine Synonyme

(array(['PKR', 'INR', 'KES', 'NIO', 'USD', 'TZS', 'PHP', 'PEN', 'XOF',
        'LRD', 'VND', 'HNL', 'MNT', 'COP', 'GTQ', 'TJS', 'BOB', 'YER',
        'KHR', 'GHS', 'SLL', 'HTG', 'CLP', 'JOD', 'UGX', 'BIF', 'IDR',
        'GEL', 'UAH', 'EUR', 'ALL', 'CRC', 'XAF', 'TRY', 'AZN', 'DOP',
        'BRL', 'MXN', 'KGS', 'AMD', 'PYG', 'LBP', 'WST', 'ILS', 'RWF',
        'ZMW', 'NPR', 'MZN', 'ZAR', 'BZD', 'SRD', 'NGN', 'VUV', 'XCD',
        'MWK', 'LAK', 'MMK', 'ZWD', 'MDL', 'SSP', 'SBD', 'CNY', 'EGP',
        'MGA', 'NAD', 'LSL', 'THB'], dtype=object),
 67)


# im Voraus 4221 NaN gefunden
df_funds['borrower_genders'].unique(), df_funds['borrower_genders'].nunique()
# Spalte muss als Feature später neu aufbereitet werden

(array(['female', 'female, female', 'female, female, female', ...,
        'female, female, male, female, female, female, female, female, female, female, male, male, female, female, male, female, female, female, female, female, female, female',
        'male, female, female, female, female, female, female, female, male, male, female, male, female, male, male, male',
        'female, female, female, male, female, female, female, male, female, female, female, male, female, male, female, female, female, female, female, female, female, female, female, female, female, female, female, female, male'],
       dtype=object),
 11298)


# Vorab keine NaN erkannt
df_funds['repayment_interval'].unique(), df_funds['repayment_interval'].nunique()
# keine Synonyme erkannt

(array(['irregular', 'bullet', 'monthly', 'weekly'], dtype=object), 4)


# Anzeige der Zeilen die NaN bei country_code enthalten
df_funds.loc[df_funds['country_code'].isnull(), ['country_code', 'country']]


# Anzeige aller Zeilen die aus Namibia sind, um country_code zu finden
df_funds.loc[df_funds['country']=='Namibia', ['country_code', 'country']]
# alle Zeilen die als NaN identifiziert wurden sind auch aus Namibia
# es wurde kein country_code gefunden


df_funds.replace({'country_code':{np.nan:'NA'}}, inplace=True)
df_funds['country_code'].isnull().sum()

0


# Check, ob NaN durch NA ersetzt wurde
df_funds.loc[df_funds['country']=='Namibia', ['country_code', 'country']]


df_funds.tail()


# Versuch die borrower gender in String zu casten
df_funds['borrower_genders'] = df_funds['borrower_genders'].astype('string')


from collections import Counter
my_string = 'male'
my_list = my_string.split(', ')
c = Counter(my_list)
c, c['male']
# Counterdict beinhaltet die Anzahl der unique keys

(Counter({'male': 1}), 1)


# Schreibe in Liste die Counterdicts pro Eintrag in borrower_genders Spalte
l_borrower_genders = []
l_others = []
s_borrower_genders = set()
for element in df_funds['borrower_genders']: # gehe durch alle Elemente von borrower_genders
    try:
        my_list = element.split(', ')   # versuch String zu splitten ob mit oder ohne Komma
        c=Counter(my_list)
        l_borrower_genders.append(c)  # Füge das Counterdict in die neue Liste
    except:
        l_others.append(element)  # füge alle anderen Einträge in eine andere Liste


Counter(l_others), len(l_borrower_genders), 4221+666984
# Check, neben NaN kommen nur Strings vor

(Counter({<NA>: 4221}), 666984, 671205)


for i in l_borrower_genders:
    for j in i.keys():
        s_borrower_genders.add(j)
s_borrower_genders
# Check die unique Keys der Dictionary keys sind nur male oder female

{'female', 'male'}


# Schreibe in neue male und female Listen die Anzahl der jeweils males und females
l_female = []
l_male = []
for element in df_funds['borrower_genders']: # gehe durch alle Elemente von borrower_genders
    try:
        my_list = element.split(', ')   # versuch String zu splitten ob mit oder ohne Komma
        c=Counter(my_list)
        l_female.append(c['female'])
        l_male.append(c['male'])
    except:
        l_male.append(0)  
        l_female.append(0)

# füge neue Listen als Spalten ein
df_funds['female_count'] = l_female
df_funds['male_count'] = l_male


df_funds.loc[df_funds['borrower_genders'].isnull(), ['borrower_genders', 'female_count', 'male_count']]


df_funds.insert(2, 'difference_amount', df_funds['loan_amount'] - df_funds['funded_amount'])
df_funds.head(1)


df_funds.insert(3, 'success_rate', df_funds['funded_amount']/df_funds['loan_amount']*100)
df_funds.head(1)


df_funds['success_rate'].unique()

array([100.        ,  85.5       ,  80.20833333, ...,  52.21238938,
        49.66442953,   8.97435897])


kategorien = ['unfunded','partially funded','funded','overfunded']
# df_funds.drop('funding_status', axis=1, inplace=True)
df_funds.insert(4, 'funding_status', pd.cut(df_funds['success_rate'],bins=[-1,0,99.9999,100,np.inf], labels=kategorien))


df_funds.loc[df_funds['success_rate']>100, ['success_rate', 'funding_status']]


# df_funds.drop('avg_lender_amount', axis=1, inplace=True)
df_funds.insert(13, 'avg_lender_amount', round(df_funds['funded_amount']/df_funds['lender_count'], 2))
df_funds.head(1)


df_funds.replace({'avg_lender_amount':{np.nan:0}}, inplace=True)


df_funds_opti = df_funds.copy()


df_funds_opti.dtypes

funded_amount          float64
loan_amount            float64
difference_amount      float64
success_rate           float64
funding_status        category
activity                object
sector                  object
use                     object
country_code            object
country                 object
region                  object
currency                object
term_in_months         float64
avg_lender_amount      float64
lender_count             int64
borrower_genders        string
repayment_interval      object
female_count             int64
male_count               int64
dtype: object


df_funds_opti.memory_usage(deep=True)

Index                      128
funded_amount          5369640
loan_amount            5369640
difference_amount      5369640
success_rate           5369640
funding_status          671645
activity              46521289
sector                43603755
use                   78857461
country_code          39601095
country               43923974
region                46078132
currency              40272300
term_in_months         5369640
avg_lender_amount      5369640
lender_count           5369640
borrower_genders      47072848
repayment_interval    43400106
female_count           5369640
male_count             5369640
dtype: int64


# Prüfe Nachkommastallen
import numbers
for c in df_funds_opti:
    for i in df_funds_opti[c]:
        if isinstance(i, numbers.Number):
            if i % 1 != 0:
                print(f'{c} hat eine Nachkommastelle!')
                break
            else:
                break

use hat eine Nachkommastelle!
region hat eine Nachkommastelle!


l_floats = ['funded_amount', 'loan_amount', 'term_in_months', 'difference_amount']
for i in l_floats:
    df_funds_opti[i] = df_funds_opti[i].astype(int)


df_funds_opti.dtypes

funded_amount            int32
loan_amount              int32
difference_amount        int32
success_rate           float64
funding_status        category
activity                object
sector                  object
use                     object
country_code            object
country                 object
region                  object
currency                object
term_in_months           int32
avg_lender_amount      float64
lender_count             int64
borrower_genders        string
repayment_interval      object
female_count             int64
male_count               int64
dtype: object


l_category = ['sector', 'repayment_interval', 'country', 'country_code', 'currency', 'activity']
for i in l_category:
    df_funds_opti[i] = df_funds_opti[i].astype('category')
df_funds_opti.dtypes

funded_amount            int32
loan_amount              int32
difference_amount        int32
success_rate           float64
funding_status        category
activity              category
sector                category
use                     object
country_code          category
country               category
region                  object
currency              category
term_in_months           int32
avg_lender_amount      float64
lender_count             int64
borrower_genders        string
repayment_interval    category
female_count             int64
male_count               int64
dtype: object


df_funds_opti.loc[:, ['success_rate', 'avg_lender_amount']] = \
df_funds_opti.loc[:, ['success_rate', 'avg_lender_amount']].apply(pd.to_numeric, downcast="float")
df_funds_opti.dtypes

funded_amount            int32
loan_amount              int32
difference_amount        int32
success_rate           float32
funding_status        category
activity              category
sector                category
use                     object
country_code          category
country               category
region                  object
currency              category
term_in_months           int32
avg_lender_amount      float32
lender_count             int64
borrower_genders        string
repayment_interval    category
female_count             int64
male_count               int64
dtype: object


# borrower_genders Spalte löschen
df_funds_opti.drop('borrower_genders', axis=1, inplace=True)
df_funds_opti.dtypes

funded_amount            int32
loan_amount              int32
difference_amount        int32
success_rate           float32
funding_status        category
activity              category
sector                category
use                     object
country_code          category
country               category
region                  object
currency              category
term_in_months           int32
avg_lender_amount      float32
lender_count             int64
repayment_interval    category
female_count             int64
male_count               int64
dtype: object


# Überblick
df_funds.memory_usage(deep=True), df_funds_opti.memory_usage(deep=True)

(Index                      128
 funded_amount          5369640
 loan_amount            5369640
 difference_amount      5369640
 success_rate           5369640
 funding_status          671645
 activity              46521289
 sector                43603755
 use                   78857461
 country_code          39601095
 country               43923974
 region                46078132
 currency              40272300
 term_in_months         5369640
 avg_lender_amount      5369640
 lender_count           5369640
 borrower_genders      47072848
 repayment_interval    43400106
 female_count           5369640
 male_count             5369640
 dtype: int64,
 Index                      128
 funded_amount          2684820
 loan_amount            2684820
 difference_amount      2684820
 success_rate           2684820
 funding_status          671645
 activity               1357871
 sector                  672752
 use                   78857461
 country_code            678442
 country                 679029
 region                46078132
 currency                677329
 term_in_months         2684820
 avg_lender_amount      2684820
 lender_count           5369640
 repayment_interval      671633
 female_count           5369640
 male_count             5369640
 dtype: int64)


# gesamte in-memory-Speicherplatzreduzierung

reduction = (df_funds.memory_usage(deep=True).sum()-df_funds_opti.memory_usage(deep=True).sum())/df_funds.memory_usage(deep=True).sum()

print(f"{reduction:0f}")

0.660146


df_funds_opti.to_pickle('funds_after_preprocessing.pkl')

	funded_amount	loan_amount	term_in_months	lender_count
count	671205.000000	671205.000000	671205.000000	671205.000000
mean	785.995061	842.397107	13.739022	20.590922
std	1130.398941	1198.660073	8.598919	28.459551
min	0.000000	25.000000	1.000000	0.000000
25%	250.000000	275.000000	8.000000	7.000000
50%	450.000000	500.000000	13.000000	13.000000
75%	900.000000	1000.000000	14.000000	24.000000
max	100000.000000	100000.000000	158.000000	2986.000000

	funded_amount	loan_amount	term_in_months	lender_count
funded_amount	1.000000	0.945044	0.149310	0.849168
loan_amount	0.945044	1.000000	0.184795	0.798697
term_in_months	0.149310	0.184795	1.000000	0.227283
lender_count	0.849168	0.798697	0.227283	1.000000

Abschlussprojekt¶

1. Teil - Theoriefragen¶

1.1) 4 V´s¶

1.2) fehlende Werte¶

2. Teil - Praxis¶

Daten einlesen¶

Mit den Daten vertraut machen¶

Fehlende Werte¶

Identifizieren¶

Beseitigen¶

Ausreißer¶

Features: Spalten mit neuem Informationsgehalt¶

borrower_genders¶

Wo wurde nicht vollständig finanziert: Differenz¶

Erfolgsquote¶

Finanzierungsstatus¶

Durchschnittlicher Betrag pro Darlehensgeber¶

Speicherplatz optimieren¶

Speicherplatz optimierte Kopie des DataFrames¶

Floats in Integer¶

Object in Category¶

Float64 in Float32¶

Spalten löschen¶

Berechnung der Reduktion des Speicherplatzes¶

Sicherung des Speicherplatzoptimierten DataFrames als .pkl-Datei¶

	,funded_amount,loan_amount,activity,sector,use,country_code,country,region,currency,term_in_months,lender_count,borrower_genders,repayment_interval
0	0,300.0,300.0,Fruits & Vegetables,Food,"To buy...
1	1,575.0,575.0,Rickshaw,Transportation,to repai...

	#funded_amount#loan_amount#activity#sector#use#country_code#country#region#currency#term_in_months#lender_count#borrower_genders#repayment_interval
0	0#175.0#175.0#Liquor Store / Off-License#Food#...
1	1#325.0#325.0#Livestock#Agriculture#to buy 3 z...

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
0	300.0	300.0	Fruits & Vegetables	Food	To buy seasonal, fresh fruits to sell.	PK	Pakistan	Lahore	PKR	12.0	12	female	irregular
1	575.0	575.0	Rickshaw	Transportation	to repair and maintain the auto rickshaw used ...	PK	Pakistan	Lahore	PKR	11.0	14	female, female	irregular
2	150.0	150.0	Transportation	Transportation	To repair their old cycle-van and buy another ...	IN	India	Maynaguri	INR	43.0	6	female	bullet
3	200.0	200.0	Embroidery	Arts	to purchase an embroidery machine and a variet...	PK	Pakistan	Lahore	PKR	11.0	8	female	irregular
4	400.0	400.0	Milk Sales	Food	to purchase one buffalo.	PK	Pakistan	Abdul Hakeem	PKR	14.0	16	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
334995	7125.0	7125.0	Plastics Sales	Retail	to stock up with a dozen plastic tubs and buck...	CD	The Democratic Republic of the Congo	Goma, North Kivu province	USD	6.0	15	female, female, female, female, female, female...	irregular
334996	125.0	125.0	Home Appliances	Personal Use	to buy a water filter to provide safe drinking...	KH	Cambodia	Phnom Penh	KHR	8.0	5	male, male, male	monthly
334997	925.0	925.0	Cattle	Agriculture	to increase his herd to get more milk and prov...	TJ	Tajikistan	Khuroson	TJS	14.0	28	male	monthly
334998	175.0	175.0	General Store	Retail	to buy additional items to sell, like beverage...	PH	Philippines	Palo, Leyte	PHP	8.0	6	female	irregular
334999	150.0	150.0	Food Production/Sales	Food	to buy sticky rice and sugar.	PH	Philippines	Jagna, Bohol	PHP	8.0	5	female	irregular

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
0	175.0	175.0	Liquor Store / Off-License	Food	to purchase additional stock of coconut wine t...	PH	Philippines	Palo, Leyte	PHP	8.0	6	female	irregular
1	325.0	325.0	Livestock	Agriculture	to buy 3 zebus and food to fatten them up.	MG	Madagascar	Antsirabe	MGA	12.0	13	female	monthly
2	550.0	550.0	Food Stall	Food	to buy ingredients for her food-vending busine...	PH	Philippines	Cordova, Cebu	PHP	5.0	6	female	irregular
3	1300.0	1300.0	Cattle	Agriculture	to buy one head of cattle.	EG	Egypt	Baniswef	EGP	14.0	50	male	monthly
4	900.0	900.0	Consumer Goods	Personal Use	to buy consumer goods amongst others.	PE	Peru	Urubamba - Urubamba - Cusco	PEN	6.0	1	female	irregular

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
336200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
336201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
336202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
336203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
336204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	funded_amount	loan_amount	activity	sector	use	country_code	country	region	currency	term_in_months	lender_count	borrower_genders	repayment_interval
671200	0.0	25.0	Livestock	Agriculture	[True, u'para compara: cemento, arenya y ladri...	PY	Paraguay	Concepción	USD	13.0	0	female	monthly
671201	25.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	1	female	monthly
671202	0.0	25.0	Games	Entertainment	NaN	KE	Kenya	NaN	KES	13.0	0	NaN	monthly
671203	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly
671204	0.0	25.0	Livestock	Agriculture	[True, u'to start a turducken farm.'] - this l...	KE	Kenya	NaN	KES	13.0	0	female	monthly

	country_code	country
202537	NaN	Namibia
202823	NaN	Namibia
344929	NaN	Namibia
351177	NaN	Namibia
420953	NaN	Namibia
421218	NaN	Namibia
487207	NaN	Namibia
487653	NaN	Namibia

	borrower_genders	female_count	male_count
140	<NA>	0	0
145	<NA>	0	0
170	<NA>	0	0
412	<NA>	0	0
414	<NA>	0	0
...	...	...	...
671151	<NA>	0	0
671174	<NA>	0	0
671178	<NA>	0	0
671185	<NA>	0	0
671202	<NA>	0	0

	success_rate	funding_status
277188	106.250000	overfunded
338159	113.333333	overfunded